﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using HZ.Data;

namespace HZ.Supervise.DAL
{
    public class DAL_MessageTypeDetail
    {
        /// <summary>
        /// 插入自定义规则
        /// </summary>
        /// <param name="MessageTypeDetal">model</param>
        /// <returns>0失败，1成功</returns>
        public static int InsertIntoMessageTypeDetail(Model.MessageTypeDetail MessageTypeDetal)
        {
            int count = 0;

            try
            {

                count = HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.MessageTypeDetail>(MessageTypeDetal);
            }
            catch (Exception ex)
            {
                count = 0;
                ex.ToString();
            }
            return count;
        }

        /// <summary>
        /// 判断规则是否重复
        /// </summary>
        /// <param name="MessageTypeDetal">model</param>
        /// <returns>ture 重复，false 不重复</returns>
        public static bool IsExistMessageTypeDetail(Model.MessageTypeDetail MessageTypeDetal)
        {


            bool bol = false;//不重复   
            try
            {
                StringBuilder sbwhere = new StringBuilder();

                //地区
                if (!string.IsNullOrEmpty(MessageTypeDetal.AreaId))
                {
                    sbwhere.AppendFormat(" and AreaId in ('{0}')", MessageTypeDetal.AreaId);
                }
                //医院
                if (!string.IsNullOrEmpty(MessageTypeDetal.HospitalId))
                {
                    sbwhere.AppendFormat(" and HospitalId in ('{0}')", MessageTypeDetal.HospitalId);
                }

                //药品类别
                if (!string.IsNullOrEmpty(MessageTypeDetal.SortId))
                {
                    sbwhere.AppendFormat(" and SortId='{0}'", MessageTypeDetal.SortId);
                }
                //规则类别编号
                if (!string.IsNullOrEmpty(MessageTypeDetal.TypeId.ToString()))
                {
                    sbwhere.AppendFormat(" and TypeId='{0}'", MessageTypeDetal.TypeId);
                }

                //用户编号
                if (!string.IsNullOrEmpty(MessageTypeDetal.UserId))
                {
                    sbwhere.AppendFormat(" and UserId='{0}'", MessageTypeDetal.UserId);
                }
                //药品编号
                if (!string.IsNullOrEmpty(MessageTypeDetal.GoodsID))
                {
                    sbwhere.AppendFormat(" and GoodsID in ('{0}')", MessageTypeDetal.GoodsID);
                }

                //预警级别
                if (!string.IsNullOrEmpty(MessageTypeDetal.Level))
                {
                    sbwhere.AppendFormat(" and Level='{0}'", MessageTypeDetal.Level);
                }

                //企业编号
                if (!string.IsNullOrEmpty(MessageTypeDetal.CompanyId))
                {
                    sbwhere.AppendFormat(" and CompanyId in ('{0}')", MessageTypeDetal.CompanyId);
                }

                //企业名称
                if (!string.IsNullOrEmpty(MessageTypeDetal.CompanyName))
                {
                    sbwhere.AppendFormat(" and CompanyName in ('{0}')", MessageTypeDetal.CompanyName);
                }


                //CountLeft
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.CountLeft)))
                {
                    sbwhere.AppendFormat(" and CountLeft = {0}", MessageTypeDetal.CountLeft);
                }
                else
                {
                    sbwhere.AppendFormat(" and CountLeft = {0}", 0);
                }
                //CountParentLeft
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.CountParentLeft)))
                {
                    sbwhere.AppendFormat(" and CountParentLeft = {0}", MessageTypeDetal.CountParentLeft);
                }
                else
                {
                    sbwhere.AppendFormat(" and CountParentLeft = {0}", 0);
                }
                //CountParentRight
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.CountParentRight)))
                {
                    sbwhere.AppendFormat(" and CountParentRight = {0}", MessageTypeDetal.CountParentRight);
                }
                else
                {
                    sbwhere.AppendFormat(" and CountParentRight = {0}", 0);
                }
                //CountRight
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.CountRight)))
                {
                    sbwhere.AppendFormat(" and CountRight = {0}", MessageTypeDetal.CountRight);
                }
                else
                {
                    sbwhere.AppendFormat(" and CountRight = {0}", 0);
                }
                //AmountLeft
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.AmountLeft)))
                {
                    sbwhere.AppendFormat(" and AmountLeft = {0}", MessageTypeDetal.AmountLeft);
                }
                else
                {
                    sbwhere.AppendFormat(" and AmountLeft = {0}", 0);
                }
                //AmountParentLeft
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.AmountParentLeft)))
                {
                    sbwhere.AppendFormat(" and AmountParentLeft = {0}", MessageTypeDetal.AmountParentLeft);
                }
                else
                {
                    sbwhere.AppendFormat(" and AmountParentLeft = {0}", 0);
                }
                //AmountParentRight
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.AmountParentRight)))
                {
                    sbwhere.AppendFormat(" and AmountParentRight = {0}", MessageTypeDetal.AmountParentRight);
                }
                else
                {
                    sbwhere.AppendFormat(" and AmountParentRight = {0}", 0);
                }
                //AmountRight
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.AmountRight)))
                {
                    sbwhere.AppendFormat(" and AmountRight = {0}", MessageTypeDetal.AmountRight);
                }
                else
                {
                    sbwhere.AppendFormat(" and AmountRight = {0}", 0);
                }
                //Days
                if (!string.IsNullOrEmpty(Convert.ToString(MessageTypeDetal.Days)))
                {
                    sbwhere.AppendFormat(" and Days = '{0}'", MessageTypeDetal.Days);
                }

                //DayType
                if (!string.IsNullOrEmpty(MessageTypeDetal.DayType))
                {
                    sbwhere.AppendFormat(" and DayType = '{0}'", MessageTypeDetal.DayType);
                }
                sbwhere.Append(" and Is_Del=0");

                string sql = string.Format(@"select MessageDetailId from TB_MessageTypeDetail where 1=1  {0}", sbwhere.ToString());
                if (HZ.Data.DbHelperFactory.Create().ExecuteTable(sql).Rows.Count > 0)
                {
                    bol = true;//重复
                }

            }
            catch (Exception ex)
            {
                //写入日志
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug(typeof(DAL_Role).FullName, "规则查询时判断规则是否重复出错", ex);
                bol = true;
            }
            return bol;

        }
        /// <summary>
        /// 取得启用的规则
        /// </summary>
        /// <returns>datatable</returns>
        public static DataTable GetMessDetailByWork()
        {
            string _sql = "select * from dbo.TB_MessageTypeDetail where 1=1 and Is_Work=1 and Is_Del=0";
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(_sql);
        }
        /// <summary>
        /// 删除禁用或者删除的消息
        /// </summary>
        /// <returns>datatable</returns>
        public static int DelMessDetailByNoWork()
        {
            string sql = "delete TB_MessageSend where MessageDetailId in (select MessageDetailId from TB_MessageTypeDetail where Is_Work = 0 or Is_Del=1)";
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 根据规则查询消息
        /// </summary>
        /// <param name="_strSql"></param>
        /// <returns></returns>
        public static DataTable GetMessageSendByMessageTypeDetail(string _strSql)
        {
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(_strSql);
        }

        /// <summary>
        /// 判断此规则的消息是否存在
        /// </summary>
        /// <param name="_strMessageTypeDetailID">规则编号</param>
        /// <param name="_strHospitalID">医院编号</param>
        /// <param name="_strCompanyID_PS">企业编号</param>
        /// <returns>false,不存在；true 存在</returns>
        public static bool IsExistMessageSend(string _strMessageTypeDetailID, string _strHospitalID, string _strCompanyID_PS, string _strGoodsID, string _strBillId)
        {
            bool bol = false;//不重复
            StringBuilder sbwhere = new StringBuilder();
            try
            {
                //详细规则编号
                if (!string.IsNullOrEmpty(_strMessageTypeDetailID))
                {
                    sbwhere.AppendFormat(" MessageDetailId='{0}'", _strMessageTypeDetailID);
                }

                //医院编号
                if (!string.IsNullOrEmpty(_strHospitalID))
                {
                    sbwhere.AppendFormat(" and HospitalId='{0}'", _strHospitalID);
                }

                //企业编号
                if (!string.IsNullOrEmpty(_strCompanyID_PS))
                {
                    sbwhere.AppendFormat(" and CompanyId_Jy='{0}'", _strCompanyID_PS);
                }
                //企业编号
                if (!string.IsNullOrEmpty(_strGoodsID))
                {
                    sbwhere.AppendFormat(" and GoodsId='{0}'", _strGoodsID);
                }
                //订单编号
                if (!string.IsNullOrEmpty(_strBillId))
                {
                    sbwhere.AppendFormat(" and BllId='{0}'", _strBillId);
                }
                string sql = string.Format(@"select * from TB_MessageSend where {0}", sbwhere.ToString());
                if (HZ.Data.DbHelperFactory.Create().ExecuteTable(sql).Rows.Count > 0)
                {
                    bol = true;//重复
                }
            }
            catch (Exception ex)
            {
                //写入日志
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug(typeof(DAL_Role).FullName, "规则查询时判断消息是否重复出错", ex);
                bol = true;
            }
            return bol;
        }

        /// <summary>
        /// 消息是否重复
        /// </summary>
        /// <param name="_strWhere">查询条件</param>
        /// <returns>true重复,false不重复</returns>
        public static bool IsExistMessageSend(string _strWhere)
        {
            string strsql = "";
            bool IsExist = true;
            strsql = "select SUM(Amount) as Amount SUM(purchasecount) as Count from dbo.View_PurchaseOrderDetail where " + _strWhere;
            DataTable dtMessage = DAL_MessageTypeDetail.GetMessageSendByMessageTypeDetail(strsql);

            IsExist = dtMessage.Rows.Count > 0 ? true : false;//存在true,不存在false
            return IsExist;
        }
        /// <summary>
        /// 更新消息表
        /// </summary>
        /// <param name="_strMessTypeDetailID">规则编号</param>
        /// <param name="_strCompanyID">企业编号</param>
        /// <param name="_strHospitalID">医院编号</param>
        /// <param name="_strCount">数量</param>
        /// <param name="_strAmount">金额</param>
        /// <returns>1成功</returns>
        public static int UpdateMessageSend(string _strMessTypeDetailID, string _strCompanyID, string _strHospitalID, string _strCount, string _strAmount)
        {
            StringBuilder sbwhere = new StringBuilder();

            //详细规则编号
            if (!string.IsNullOrEmpty(_strMessTypeDetailID))
            {
                sbwhere.AppendFormat(" MessageDetailId='{0}'", _strMessTypeDetailID);
            }

            //医院编号
            if (!string.IsNullOrEmpty(_strHospitalID))
            {
                sbwhere.AppendFormat(" and HospitalId='{0}'", _strHospitalID);
            }

            //企业编号
            if (!string.IsNullOrEmpty(_strCompanyID))
            {
                sbwhere.AppendFormat(" and CompanyId_Jy='{0}'", _strCompanyID);
            }
            string sql = string.Format(@"update TB_MessageSend set Numbers=" + _strCount + " ,Amount=" + _strAmount + " where {0}", sbwhere.ToString());

            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);

        }

        /// <summary>
        /// 插入消息表
        /// </summary>
        /// <param name="_strMessageTypeDetalID">详细规则编号</param>
        /// <param name="_strTypeID">规则类型</param>
        /// <param name="_strTypeName">类型名称</param>
        /// <param name="_strTableID">所查主键</param>
        /// <param name="_strTableName">所查表名</param>
        /// <param name="_strUserId">用户编号</param>
        /// <param name="CreatTime">创建时间</param>
        /// <param name="_strIsRead">是否已读</param>
        /// <param name="_strAreaID">地区编号</param>
        /// <param name="_strHospitalID">医院编号</param>
        /// <param name="_strHospitalName">医院名称</param>
        /// <param name="_strCompanyID">企业编号</param>
        /// <param name="_strCompanyName">企业名称</param>
        /// <param name="_strSortID">药品类别编号</param>
        /// <param name="_strSortName">类别名称</param>
        /// <param name="_strNumbers">预警数量</param>
        /// <param name="_strAmount">预警金额</param>
        /// <param name="_strDays">时间范围</param>
        /// <param name="_strDayType">时间类型</param>
        /// <param name="_strGoodsID">药品编号</param>
        /// <param name="_strProductName">药品名称</param>
        /// <param name="_strBillID">单据编号</param>
        /// <returns></returns>
        public static int InsertIntoMessageSend(string _strMessageTypeDetalID, string _strTypeID, string _strTypeName, string _strTableID, string _strTableName, string _strUserId, DateTime CreatTime, string _strIsRead, string _strAreaID, string _strHospitalID, string _strHospitalName, string _strCompanyID, string _strCompanyName, string _strSortID, string _strSortName, string _strNumbers, string _strAmount, string _strDays, string _strDayType, string _strGoodsID, string _strProductName, string _strBillID, string _strHistory)
        {
            string Id = Guid.NewGuid().ToString();
            string sql = string.Format(@"insert into TB_MessageSend (Id,Name,MessageDetailId,TableId,TableName,Content,UserId,CreateTime,Is_Read,AreaId,AreaName,HospitalId,HospitalName,CompanyId_Jy,CompanyName_Jy,SortId,SortName,Numbers,Amount,Days,DayType,GoodsId,ProductName,BllId,TypeId) 
                                        values ('" + Id + "','" + _strTypeName + "','" + _strMessageTypeDetalID + "','OrderDetailID','View_PurchaseOrderDetail','消息内容','" + _strUserId + "','" + DateTime.Now + "','" + false + "','" + _strAreaID + "','" + null + "','" + _strHospitalID + "','" + _strHospitalName + "','" + _strCompanyID + "','" + _strCompanyName + "','" + _strSortID + "','" + _strSortName + "','" + _strNumbers + "','" + _strAmount + "','" + _strDays + "','" + _strDayType + "','" + _strGoodsID + "','" + _strProductName + "','" + _strBillID + "','" + _strTypeID + "') ");

            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
        }

        public static int InsertIntoMessageSend(Model.MessageSend MessageSend)
        {
            int count = 0;

            try
            {

                count = HZ.Data.DAL_Helper.InsertModel<HZ.Supervise.Model.MessageSend>(MessageSend);
            }
            catch (Exception ex)
            {
                count = 0;
                ex.ToString();
            }
            return count;
        }
        /// <summary>
        /// 获得预警规则
        /// </summary>
        /// <param name="messageTypeDetail">规则编号</param>
        /// <param name="IsWork">true 启用，false 不启用</param>
        /// <param name="type">""全部， "company"企业，"hospital"医院</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">分页数量</param>
        /// <param name="totalrecord">总数量</param>
        /// <returns></returns>
        public static DataTable GetMessageTypeDetail(Model.MessageTypeDetail messageTypeDetail, bool IsWork, string type, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sbwhere = new StringBuilder("1=1");
            if (type == "")
            {
                if (!string.IsNullOrEmpty(messageTypeDetail.HospitalName))
                {
                    sbwhere.AppendFormat(" AND HospitalName Like '%{0}%'", messageTypeDetail.HospitalName);
                }
            }

            if (type == "hospital")
            {
                sbwhere.Append("AND CompanyName is null");
            }

            if (!string.IsNullOrEmpty(messageTypeDetail.UserId))
            {
                sbwhere.AppendFormat(" AND UserId = '{0}'", messageTypeDetail.UserId);
            }
            if (!string.IsNullOrEmpty(messageTypeDetail.AreaId))
            {
                string areaid = "'" + messageTypeDetail.AreaId.Replace(" ", "").Replace(",", "','") + "'";
                sbwhere.AppendFormat(" AND AreaID IN ({0})", areaid);
            }
            if (type == "")
            {
                if (!string.IsNullOrEmpty(messageTypeDetail.CompanyName))
                {
                    sbwhere.AppendFormat(" AND CompanyName Like '%{0}%'", messageTypeDetail.CompanyName);
                }
            }

            if (type == "company")
            {
                sbwhere.Append("AND HospitalName is null");
            }

            if (!string.IsNullOrEmpty(messageTypeDetail.SortName))
            {
                sbwhere.AppendFormat(" AND SortName Like '%{0}%'", messageTypeDetail.SortName);
            }
            if (!string.IsNullOrEmpty(messageTypeDetail.ProductName))
            {
                 sbwhere.AppendFormat(" AND ProductName Like '%{0}%'", messageTypeDetail.ProductName);
            }

            if (!string.IsNullOrEmpty(messageTypeDetail.Level))
            {
                sbwhere.AppendFormat(" AND Level = '{0}'", messageTypeDetail.Level);
            }
            if (IsWork)
            {
                sbwhere.AppendFormat(" AND Is_Work = 1");
            }
            else
            {
                sbwhere.AppendFormat(" AND Is_Work = 0");
            }
            string sql = string.Format("SELECT * FROM dbo.TB_MessageTypeDetail WHERE {0} ORDER BY HospitalName", sbwhere.ToString());
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;


            return des.GetSpecailPage();
        }

        public static DataTable GetMessageTypeDetailByType(Model.MessageTypeDetail messageTypeDetail, string type, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sbwhere = new StringBuilder("1=1");
            if (!string.IsNullOrEmpty(messageTypeDetail.UserId))
            {
                sbwhere.AppendFormat(" AND UserId = '{0}'", messageTypeDetail.UserId);
            }
            if (!string.IsNullOrEmpty(messageTypeDetail.Is_Del.ToString()))
            {
                sbwhere.AppendFormat(" AND Is_Del = '{0}'", messageTypeDetail.Is_Del);  //数据库中无此字段故屏蔽
            }
            if (!string.IsNullOrEmpty(messageTypeDetail.Is_Work.ToString()))
            {
                sbwhere.AppendFormat(" AND Is_Work = '{0}'", messageTypeDetail.Is_Work);
            }
            if (!string.IsNullOrEmpty(type))
            {
                sbwhere.AppendFormat(" AND TypeId in ({0})", type);
            }
            //2011.4.7
            //赵洁 
            //预警数量
            if (messageTypeDetail.CountRight > 0)
            {
                sbwhere.AppendFormat(" and countright<={0}", messageTypeDetail.CountRight);
            }

            //预警金额
            if (messageTypeDetail.AmountRight > 0)
            {
                sbwhere.AppendFormat(" and amountright<={0}", messageTypeDetail.AmountRight);
            }

            //预警级别
            if (!string.IsNullOrEmpty(messageTypeDetail.Level) && !messageTypeDetail.Level.ToString().Trim().Equals("--全部--"))
            {
                sbwhere.AppendFormat(" and Level='{0}'", messageTypeDetail.Level);
            }

            string sql = string.Format("SELECT * FROM dbo.TB_MessageTypeDetail WHERE {0} ORDER BY AddTime desc", sbwhere.ToString());
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;


            return des.GetSpecailPage();
        }

        /// <summary>
        /// 获得预警消息
        /// </summary>
        /// <param name="messageTypeDetail"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessage(Model.Message message, bool IsRead, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sbwhere = new StringBuilder("1=1");
            if (!string.IsNullOrEmpty(message.Name))
            {
                sbwhere.AppendFormat(" AND a.Name like '%{0}%'", DAL_Helper.Replace(message.Name));
            }
            if (!string.IsNullOrEmpty(message.CreatTime))
            {
                sbwhere.AppendFormat(" AND a.CreateTime >= '{0}'", message.CreatTime + " 00:00:00.000");
                sbwhere.AppendFormat(" AND a.CreateTime <= '{0}'", message.CreatTime + " 23:59:59.999");
            }
            if (!string.IsNullOrEmpty(message.AreaName))
            {
                sbwhere.AppendFormat(" AND a.AreaName like '%{0}%'", DAL_Helper.Replace(message.AreaName));
            }
            if (!string.IsNullOrEmpty(message.HospitalName))
            {
                sbwhere.AppendFormat(" AND a.HospitalName like '%{0}%'", DAL_Helper.Replace(message.HospitalName));
            }
            if (!string.IsNullOrEmpty(message.ProductName))
            {
                sbwhere.AppendFormat(" AND a.ProductName like '%{0}%'", DAL_Helper.Replace(message.ProductName));
            }
            if (!string.IsNullOrEmpty(message.CompanyName_Jy))
            {
                sbwhere.AppendFormat(" AND a.CompanyName_Jy like '%{0}%'", DAL_Helper.Replace(message.CompanyName_Jy));
            }
            if (!string.IsNullOrEmpty(message.UserId))
            {
                sbwhere.AppendFormat(" AND a.UserId = '{0}'", message.UserId);
            }
            if (!string.IsNullOrEmpty(message.TypeId))
            {
                sbwhere.AppendFormat(" AND a.TypeId = '{0}'", message.TypeId);
            }

            if (IsRead)
            {
                sbwhere.AppendFormat(" AND b.Is_Read = 1");
            }
            else
            {
                sbwhere.AppendFormat(" AND b.Is_Read = 0");
            }
            string sql = string.Format("SELECT a.*,b.SqlDescription FROM dbo.TB_MessageSend  a left join TB_MessageTypeDetail b on a.MessageDetailId = b.MessageDetailId WHERE {0} ORDER BY HospitalName", sbwhere.ToString());
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;


            return des.GetSpecailPage();
        }
        /// <summary>
        ///查询预警信息详情（包括采购详情）
        /// </summary>
        /// <param name="id">预警信息id</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessageOrderDetail(string id, int currentpage, int pagesize, out int totalrecord)
        {
            string strsql = "select SUM(Amount) as Amount ,SUM(purchasecount) as Count ,HospitalName,orderName from dbo.View_PurchaseOrderDetail  where " + id + " group by HospitalName,orderName order by HospitalName";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 查询预警信息详情(处方单药品预警)
        /// </summary>
        /// <param name="messSend">model</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessageOrderDetailBySubsidy(string id, int currentpage, int pagesize, out int totalrecord)
        {
            string strsql = "select  SUM(Pamount) as Amount ,SUM(Pcount) as Count ,PHospitalName,PAreaName from View_Prescription where " + id + " group by PHospitalName,PAreaName order by PHospitalName";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 查询预警信息详情(处方单预警)
        /// </summary>
        /// <param name="messSend">model</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessageOrderDetailBySubsidy(string where, string having, int currentpage, int pagesize, out int totalrecord)
        {
            string strsql = "select max(Prescriptionid) as Prescriptionid, max(PrescriptionName) as PrescriptionName,max(PHospitalName) as PHospitalName,max(PSubmitTime) as PSubmitTime,PAreaName, SUM(Pamount) as Amount , count(1) as Count from View_Prescription where " + where + "  group by PrescriptionId,PAreaName having " + having + " order by PSubmitTime";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 查询处方单详细
        /// </summary>
        /// <param name="pid">处方单编号</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessagePrescriptionDetail(string pid, int currentpage, int pagesize, out int totalrecord, out Model.Prescription p)
        {
            string strsql = "select * from TB_PrescriptionDetail where Prescriptionid = '" + pid + "' order by PrescriptionDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(strsql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            p = new HZ.Supervise.Model.Prescription();
            string sql = "select * from TB_Prescription where PrescriptionId = '" + pid + "'";
            IList<Model.Prescription> list = DAL_Helper.CommonFillList<Model.Prescription>(HZ.Data.DbHelperFactory.Create().ExecuteTable(sql));
            p = list[0];
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 获得预警消息表中的采购单编号
        /// </summary>
        /// <param name="id">当前预警信息编号</param>
        /// <returns>并将返回的编号如"id1,id2,id3"转化为："'id1','id2','id3'"以方便进行in查询</returns>
        static string GetBllIdInMessage(string id)
        {
            string bllId = null;
            //获得当前信息编号下的采购单编号
            string sqlForGetBllId = string.Format("select BllId from dbo.TB_MessageSend where id = '{0}'", id);
            string bllIds = HZ.Data.DbHelperFactory.Create().ExecuteScalar(sqlForGetBllId).ToString();
            string[] bllIds2 = bllIds.Split(',');
            for (int i = 0; i < bllIds2.Length; i++)
            {
                if (i != bllIds2.Length - 1)
                {
                    bllId += "'" + bllIds2[i] + "',";
                }
                else
                {
                    bllId += "'" + bllIds2[i] + "'";
                }
            }
            return bllId;
        }

        /// <summary>
        /// 根据id删除预警
        /// </summary>
        /// <param name="messageTypeDetailId"></param>
        /// <returns>1成功，0失败</returns>
        public static int DeleteMessageTypeDetail(string messageTypeDetailId)
        {
            var idb = HZ.Data.DbHelperFactory.Create();
            int count = 0;
            try
            {
                idb.BeginTrans();
                string sql = "update TB_MessageTypeDetail set Is_Del=1 where MessageDetailId = '" + messageTypeDetailId + "'";

                idb.ExecuteNonQuery(sql);
                idb.CommitTrans();
                count = 1;
            }
            catch (Exception ex)
            {
                count = 0;
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug(typeof(DAL_Role).FullName, "删除消息失败", ex);
            }
            return count;

        }

        /// <summary>
        /// 根据id删除消息
        /// </summary>
        /// <param name="messageTypeDetailId"></param>
        /// <returns>1成功，0失败</returns>
        public static int DeleteMessage(string id)
        {
            var idb = HZ.Data.DbHelperFactory.Create();
            int count = 0;
            try
            {
                idb.BeginTrans();
                string sql = "delete from TB_MessageSend where id = '" + id + "'";
                idb.ExecuteNonQuery(sql);
                idb.CommitTrans();
                count = 1;
            }
            catch (Exception ex)
            {
                count = 0;
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug(typeof(DAL_Role).FullName, "删除消息失败", ex);
            }
            return count;
        }

        /// <summary>
        /// 改变预警机制状态
        /// </summary>
        /// <param name="messageDetailId">id</param>
        /// <param name="isWork">0,false;1.true</param>
        /// <returns>1成功，0失败</returns>
        public static int WarningSwitch(string messageDetailId, string isWork)
        {
            string sql = "update TB_MessageTypeDetail set Is_Work = '" + isWork + "' where MessageDetailId = @MessageDetailId";
            SqlParameter parameter = new SqlParameter("@MessageDetailId", messageDetailId);
            return DbHelperFactory.Create().ExecuteNonQuery(CommandType.Text, sql, parameter);
        }

        /// <summary>
        /// 更新规则详细表
        /// </summary>
        /// <param name="messTypeDetail">model</param>
        /// <returns>1成功，0失败</returns>
        public static int UpdateMessageTypeDetail(Model.MessageTypeDetail messTypeDetail)
        {
            string sql = "update TB_MessageTypeDetail set CountRight = '" + messTypeDetail.CountRight + "',CountLeft='" + messTypeDetail.CountLeft + "',AmountLeft='" + messTypeDetail.AmountLeft + "',AmountRight='" + messTypeDetail.AmountRight + "',Days='" + messTypeDetail.Days + "' where MessageDetailId = '" + messTypeDetail.MessageDetailId + "'";

            return DbHelperFactory.Create().ExecuteNonQuery(sql);
        }

        public static int SetMessageIsRead(string id)
        {
            string sql = "update TB_MessageTypeDetail set Is_Read ='true' where MessageDetailId = '" + id + "'";
            sql += "  update TB_MessageSend set Is_Read ='true' where MessageDetailId = '" + id + "'";
            return DbHelperFactory.Create().ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 取得规则,升序排列
        /// </summary>
        /// <returns>datatable</returns>
        public static DataTable GetMessageType()
        {
            string sql = "SELECT * FROM dbo.TB_MessageType order by TypeId asc";
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);

        }


        /// <summary>
        /// 根据ID查找规则条件
        /// </summary>
        /// <param name="_strMessageTypeDetailId"></param>
        /// <returns></returns>
        public static string GetMessageTypeDeatilSqlStringWhere(string _strMessageTypeDetailId)
        {
            string sql = "SELECT SqlStringWhere FROM dbo.TB_MessageTypeDetail where MessageDetailId='" + _strMessageTypeDetailId + "'";
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString();
        }
        /// <summary>
        /// 根据ID查找规则条件
        /// </summary>
        /// <param name="_strMessageTypeDetailId"></param>
        /// <returns></returns>
        public static string GetMessageTypeDeatilSqlStringHaving(string _strMessageTypeDetailId)
        {
            string sql = "SELECT SqlStringHaving FROM dbo.TB_MessageTypeDetail where MessageDetailId='" + _strMessageTypeDetailId + "'";
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString();
        }
        /// <summary>
        /// 将信息插入历史表
        /// </summary>
        /// <param name="_strMessageDeatilId">详细规则</param>
        /// <returns></returns>
        public static int InsertIntoMessageSendHistory(string _strMessageDeatilId)
        {

            string sql = "insert into dbo.TB_MessageSendHistory select * from dbo.TB_MessageSend where MessageDetailId='" + _strMessageDeatilId + "'";
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除消息表数据
        /// </summary>
        /// <param name="_strMessageDeatilId">详细规则编号</param>
        /// <returns>0失败，1成功</returns>
        public static int DeleteMessageSend(string _strMessageDeatilId)
        {
            var idb = HZ.Data.DbHelperFactory.Create();
            int count = 0;
            try
            {
                idb.BeginTrans();
                string sql = "delete from  dbo.TB_MessageSend  where MessageDetailId='" + _strMessageDeatilId + "'";
                idb.ExecuteNonQuery(sql);
                idb.CommitTrans();
                count = 1;
            }
            catch (Exception ex)
            {
                count = 0;
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug(typeof(DAL_Role).FullName, "删除消息失败", ex);
            }
            return count;
        }

        public static int DeleteMessageSendHistory(string _strMessageDeatilId)
        {
            var idb = HZ.Data.DbHelperFactory.Create();
            int count = 0;
            try
            {
                idb.BeginTrans();
                string sql = "delete from dbo.TB_MessageSendHistory where MessageDetailId='" + _strMessageDeatilId + "'";
                idb.ExecuteNonQuery(sql);
                idb.CommitTrans();
                count = 1;
            }
            catch (Exception ex)
            {
                count = 0;
                idb.RollTrans();
                HZ.Common.log4netManager.LogDebug(typeof(DAL_Role).FullName, "删除历史消息失败", ex);
            }
            return count;
        }

        public static DataTable GetMessageType(Model.MessageTypeDetail messType, int IsRead, int currentpage, int pagesize, out int totalrecord)
        {
            StringBuilder sb = new StringBuilder();
            if (messType.TypeId == 6 || messType.TypeId == 7)
            {
                sb.Append("select aa.HospitalName,COUNT(aa.HospitalName) as num,aa.TypeName,aa.AddTime,aa.MessageDetailId,aa.SqlDescription  from");
                sb.Append("(select m.HospitalName,m.TypeName,m.AddTime,m.MessageDetailId,m.SqlDescription  from TB_MessageTypeDetail as m ,TB_MessageSend as  s where m.MessageDetailId=s.MessageDetailId and m.TypeId=" + messType.TypeId + " and m.UserId='" + messType.UserId + "'");
                if (messType.Is_Work == true)
                {
                    sb.Append(" and m.Is_Work=1 ");
                }
                else
                {
                    sb.Append(" and m.Is_Work=0 ");
                }
                if (messType.Is_Del == true)
                {
                    sb.Append(" and m.Is_Del=1 ");
                }
                else
                {
                    sb.Append(" and m.Is_Del=0 ");
                }
                if (IsRead == 0)
                {
                    sb.Append(" and m.Is_Read=0 ");
                }
                else
                {
                    sb.Append(" and m.Is_Read=1 ");
                }
                sb.Append(") aa group by aa.HospitalName ,aa.TypeName,aa.AddTime,aa.MessageDetailId,aa.SqlDescription  order by TypeName");
            }
            else
            {

                sb.Append("select aa.CompanyName,COUNT(aa.CompanyName) as num,aa.TypeName,aa.AddTime,aa.MessageDetailId,aa.SqlDescription  from");
                sb.Append("(select m.CompanyName,m.TypeName,m.AddTime,m.MessageDetailId,m.SqlDescription  from TB_MessageTypeDetail as m ,TB_MessageSend as  s where m.MessageDetailId=s.MessageDetailId and m.TypeId=" + messType.TypeId + " and m.UserId='" + messType.UserId + "'");
                if (messType.Is_Work == true)
                {
                    sb.Append(" and m.Is_Work=1 ");
                }
                else
                {
                    sb.Append(" and m.Is_Work=0 ");
                }
                if (messType.Is_Del == true)
                {
                    sb.Append(" and m.Is_Del=1 ");
                }
                else
                {
                    sb.Append(" and m.Is_Del=0 ");
                }
                if (IsRead == 0)
                {
                    sb.Append(" and m.Is_Read=0 ");
                }
                else
                {
                    sb.Append(" and m.Is_Read=1 ");
                }
                sb.Append(") aa group by aa.CompanyName ,aa.TypeName,aa.AddTime,aa.MessageDetailId,aa.SqlDescription  order by TypeName");
            }

            string _strSql = sb.ToString();
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(_strSql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 取得未查看的消息
        /// </summary>
        /// <param name="messSend">model</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessagSend(Model.MessageSend messSend, int currentpage, int pagesize, out int totalrecord)
        {
            string _strSql = "select distinct CompanyName_Jy,CompanyId_Jy,MessageDetailId  from TB_MessageSend where MessageDetailId='" + messSend.MessageDetailId + "' and Is_Read=1 order by CompanyName_Jy";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(_strSql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 新添加的方法 赵洁
        /// </summary>
        /// <param name="messSend"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMessagSendNew(Model.MessageSend messSend, int isRead, int currentpage, int pagesize, out int totalrecord)
        {
            string _strSql = @"select distinct CompanyName_Jy,CompanyId_Jy,TB_MessageSend.MessageDetailId,TB_MessageTypeDetail.SqlDescription  from TB_MessageSend
             left join TB_MessageTypeDetail on TB_MessageSend.MessageDetailId=TB_MessageTypeDetail.MessageDetailId
where TB_MessageSend.typeid='" + messSend.TypeId + "' and TB_MessageTypeDetail.Is_Read=" + isRead + " and TB_MessageTypeDetail.UserId='" + messSend.UserId + "' order by CompanyName_Jy";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(_strSql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 取得未查看的消息
        /// </summary>
        /// <param name="messSend">model</param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetDetailMessagSend(Model.MessageSend messSend, int currentpage, int pagesize, out int totalrecord)
        {
            string _strSql = "select distinct AreaName,HospitalName,MessageDetailId  from TB_MessageSend where MessageDetailId='" + messSend.MessageDetailId + "' and Is_Read=1 and CompanyId_Jy='" + messSend.CompanyId_Jy + "' order by AreaName,HospitalName,MessageDetailId";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(_strSql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 配送企业已确认采购单未发货
        /// 医院已提交配送企业未确认
        /// 2011.4.8 赵洁
        /// </summary>
        /// <param name="messSend"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetDetailMessagSendNew(Model.MessageSend messSend, int isRead, int currentpage, int pagesize, out int totalrecord)
        {
            string _strSql = @"
  select distinct CompanyName_Jy,CompanyId_Jy,TB_MessageSend.MessageDetailId,TB_MessageTypeDetail.SqlDescription,TB_MessageTypeDetail.HospitalName,TB_MessageTypeDetail.sqlstringwhere  from TB_MessageSend
   left join Tb_MessageTypeDetail on TB_MessageSend.MessageDetailId=TB_MessageTypeDetail.MessageDetailId where TB_MessageSend.typeId='" + messSend.TypeId + "' and TB_MessageTypeDetail.Is_Read=" + isRead + " and TB_MessageTypeDetail.UserId='" + messSend.UserId + "' order by CompanyName_Jy";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(_strSql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }



        /// <summary>
        /// 弹出窗口提示信息数据
        /// 2011.4.10 赵洁
        /// </summary>
        /// <returns>datatable</returns>
        public static DataTable GetMessageForWindow()
        {
            string sql = @" select TypeId,TypeName,UserId,MessageDetailId  from TB_MessageTypeDetail 
       where Is_Del=0 and Is_Work=1 and Is_Read=0
       and MessageDetailId in (select MessageDetailId from TB_MessageSend)
          order by TypeId";
            return HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
        }



        public static int AddMessageSend_Bath(IList<Model.MessageSend> mess)
        {

            int Count = 0;
            HZ.Data.IDbHelper idbh = HZ.Data.DbHelperFactory.Create();
            try
            {
                idbh.BeginTrans();
                string temptable = "[#" + Guid.NewGuid().ToString() + "]"; // 临时表名

                // 创建临时表
                string sql1 = string.Format(@"CREATE TABLE {0} ([Id] [varchar](255) NOT NULL PRIMARY KEY ,
	                                                            [Name] [varchar](255) NULL,
	                                                            [MessageDetailId] [varchar](255) NULL,
	                                                            [TableId] [varchar](255) NULL,
	                                                            [TableName] [varchar](255) NULL,
	                                                            [Content] [varchar](max) NULL,
	                                                            [UserId] [varchar](255) NULL,
	                                                            [CreateTime] [datetime] NULL,
	                                                            [Is_Read] [bit] NULL,
	                                                            [AreaId] [varchar](255) NULL,
	                                                            [AreaName] [varchar](50) NULL,
	                                                            [HospitalId] [varchar](255) NULL,
                                                                [HospitalName] [varchar](255) NULL,   
	                                                            [CompanyId_Jy] [varchar](255) NULL,
	                                                            [CompanyName_Jy] [varchar](255) NULL,
	                                                            [SortId] [varchar](255) NULL,
	                                                            [SortName] [varchar](255) NULL,
	                                                            [Numbers] [bigint] NULL,
	                                                            [Amount] [decimal](18,0) NULL,
	                                                            [Days] [int] NULL,
	                                                            [DayType] [varchar](50) NULL,
	                                                            [GoodsId] [varchar](255) NULL,  
	                                                            [ProductName] [varchar](255) NULL,
	                                                            [BllId] [varchar](255) NULL,
	                                                            [TypeId] [nchar](10) NULL )", temptable);
                idbh.ExecuteNonQuery(sql1);

                foreach (var item in mess)
                {
                    idbh.ExecuteNonQuery(HZ.Data.DAL_Helper.GetInsertSql(item).Replace("TB_MessageSend", temptable));
                }
                // 插入临时表数据
                string sql2 = string.Format(@"INSERT INTO dbo.TB_MessageSend (Id, Name, MessageDetailId, TableId, TableName, Content, UserId, CreateTime, Is_Read, AreaId, AreaName, HospitalId, HospitalName, CompanyId_Jy, CompanyName_Jy, SortId, SortName, Numbers,Amount,Days,DayType,GoodsId,ProductName,BllId,TypeId ) SELECT * FROM {0}", temptable);
                idbh.ExecuteNonQuery(sql2);
                idbh.ExecuteNonQuery(string.Format("Drop Table {0}", temptable));

                idbh.CommitTrans();
                Count = 1;
            }
            catch (Exception ee)
            {
                idbh.RollTrans();
                Count = 0;
            }

            return Count;
        }

        /// <summary>
        /// 获取当前用户的全部预警消息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static DataTable GetMessagSendAll(string userid)
        {
            string sql = "select [Level],COUNT(1) as count from TB_MessageSend where UserId = '" + userid + "' and is_read = 0 group by [Level]";
            return Data.DbHelperFactory.Create().ExecuteTable(sql);
        }

        #region 2011.4.8-4.9 赵洁
        /// <summary>
        /// 配送企业已发货未开票
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <returns></returns>
        public static DataTable GetBllByType9(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"  select distinct d.*,m.BllId from TB_MessageSend as m left join View_Distribution as d
  on m.BllId=d.OrderDetailID where m.typeId=9 and d.IsInvoice=0 and d.DistributeCount>0 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by OrderDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 配送企业已确认采购单未发货
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <returns></returns>
        public static DataTable GetBllByType8(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"  select distinct v.*,m.bllId from TB_MessageSend as m left join View_PurchaseOrderDetail as v
 on m.BllId = v.OrderDetailID
 where m.typeId=8 and v.OrderDetailState=2 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by OrderDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 医院已提交采购单企业未确认
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetBllByType3(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"  select distinct v.*,m.bllId from TB_MessageSend as m left join View_PurchaseOrderDetail as v
 on m.BllId = v.OrderDetailID
 where m.typeId=3 and v.OrderDetailState=1 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by OrderDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 医院已入库未回款
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetBllByType7(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"  select distinct v.*,m.bllId from TB_MessageSend as m left join View_PurchaseOrderDetail as v
 on m.BllId = v.OrderDetailID
 where m.typeId=7 and WarehouseCount>0 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by OrderDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 配送企业已确认医院未入库
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetBllByType6(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"  select distinct v.*,m.bllId from TB_MessageSend as m left join View_PurchaseOrderDetail as v
 on m.BllId = v.OrderDetailID
 where m.typeId=6 and v.OrderDetailState=2 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by OrderDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }


        /// <summary>
        /// 医院已提交退货单企业未确认
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetBllByType4(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"   select distinct r.*,m.BllId from TB_MessageSend as m left join 
  dbo.View_ReturnMerchandiseDetail as r
  on m.BllId=r.ReturnSerialDetailID
  where ReturnState=0 and m.typeId=4 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by ReturnSerialDetailID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 医院已结算配送企业未确认
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetBllByType5(string orderDetailId, string hospitalName, string areaName, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (!string.IsNullOrEmpty(areaName))
                where = " and m.areaName='" + areaName + "'";

            string sql = @"   select distinct i.*,m.BllId from TB_MessageSend as m left join 
  dbo.View_InvoiceDistribution as i
  on m.BllId=i.InvoicePaySerialID
  where i.IsSubmit=1 and m.typeId=5 and m.bllId in (select BllId from TB_MessageSend where HospitalName='" + hospitalName + "' ) and m.hospitalName='" + hospitalName + "' " + where + " order by InvoicePaySerialID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }




        /// <summary>
        /// 医药监控
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetMedicinModel(string hospitalName, string orderName, int currentpage, int pagesize, out int totalrecord)
        {

            string sql = @"   select distinct OrderName,ProductName,MedicineModelName,Price,ExecuteTime,Unit  from dbo.View_PurchaseOrderDetail
where HospitalName='" + hospitalName + "' and orderName='" + orderName + "' order by OrderName";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 药品监控
        /// 查询where条件
        /// </summary>
        /// <returns></returns>
        public static DataTable GetWhere(int isRead)
        {
            string sql = "select sqlstringwhere from TB_MessageTypeDetail where typeid=1 and is_read=" + isRead;
            return Data.DbHelperFactory.Create().ExecuteTable(sql);
        }


        /// <summary>
        /// 药品监控，根据条件查询所有的数据
        /// </summary>
        /// <param name="orderDetailId"></param>
        /// <param name="hospitalName"></param>
        /// <param name="areaName"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable GetAll(DataTable dt, int currentpage, int pagesize, out int totalrecord)
        {
            string where = "";
            if (dt.Rows.Count > 0)
            {
                where = " where ";
                if (dt.Rows.Count == 1)
                    where += dt.Rows[0]["sqlStringWhere"].ToString();
                else
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        where += "(" + dt.Rows[i]["sqlStringWhere"].ToString() + ") or";
                    }
                    where = where.Substring(0, where.LastIndexOf("or"));
                }
            }
            string sql = @"  select distinct Sum(PurchaseCount) as PurchaseCount,Sum(Amount) as Amount, HospitalName 
    from View_PurchaseOrderDetail
" + where + " group by HospitalName order by HospitalName";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        #endregion
    }
}
